-- 2012-05-17 EM
alter procedure sp_get_operator_by_phone 
	@phone varchar(20), 
	@oper_code varchar(12) output,
	@oper_title varchar(200) output
as
begin
	set nocount on;
	
	declare @def varchar(3); 
	declare @num varchar(7);

	set @phone = reverse(substring(reverse(@phone), 1, 10));
	set @def = substring(@phone, 1, 3)
	set @num = substring(@phone, 4, 7)

	set @oper_code = null;
	set @oper_title = null;
	
	select @oper_code = o.code, @oper_title = o.title
	  from dbdata..def_codes c, dbdata..operators o
	 where c.operator_id = o.id
	   and @def = c.def 
	   and @num between c.beg_code and c.end_code 
	   and c.dt_removed is null 
	   and getdate() between c.dt_beg and c.dt_end
	 order by def, beg_code;
end;
go

grant execute on sp_get_operator_by_phone to Executer;

/*
USE [dbproc]
GO

DECLARE	@return_value int,
		@oper_code varchar(12),
		@oper_title varchar(200)

EXEC	@return_value = [dbo].[sp_get_operator_by_phone]
		@phone = N'79219534713',
		@oper_code = @oper_code OUTPUT,
		@oper_title = @oper_title OUTPUT

SELECT	@oper_code as N'@oper_code',
		@oper_title as N'@oper_title'

SELECT	'Return Value' = @return_value

*/